from flask import g, redirect, render_template, request, url_for, flash
import json
import os
from flask_login import login_user
from redash import settings
from redash.authentication.org_resolving import current_org
from redash.handlers.base import routes
from redash.models import Group, Organization, User, db, Workflow_state,Enums,Tabletype,Templates,GlobalSetting
from redash.tasks.general import subscribe
from wtforms import BooleanField, Form, PasswordField, StringField,IntegerField, validators
from wtforms.fields.html5 import EmailField


class SetupForm(Form):
    name = StringField("用户名", validators=[validators.InputRequired()])
    code = StringField("用户编码", validators=[validators.InputRequired()])
    email = EmailField("电子邮箱", validators=[validators.Email()])
    mobile = StringField("手机号码", validators=[validators.Length(11), validators.Optional()])
    password = PasswordField("密码", validators=[validators.Length(6)])
    org_name = StringField("单位名称", validators=[validators.InputRequired()])
    security_notifications = BooleanField()
    newsletter = BooleanField()
    if settings.MULTI_ORG:
        org_slug = StringField("单位代码", validators=[validators.InputRequired()])

def create_org(org_name, user_name, code, email, mobile, password, org_slug="default"):
    default_org = Organization(name=org_name, slug=org_slug, settings={})
    admin_group = Group(
        name="admin",
        permissions=["admin", "super_admin"],
        org=default_org,
        type=Group.BUILTIN_GROUP,
    )
    default_group = Group(
        name="default",
        permissions=Group.DEFAULT_PERMISSIONS,
        org=default_org,
        type=Group.BUILTIN_GROUP,
    )
    audit_group = Group(
        name="audit",
        permissions=["audit"],
        org=default_org,
        type=Group.BUILTIN_GROUP,
    )
    seal_group = Group(
        name="seal",
        permissions=["seal"],
        org=default_org,
        type=Group.BUILTIN_GROUP,
    )
    record_group = Group(
        name="record",
        permissions=["record"],
        org=default_org,
        type=Group.BUILTIN_GROUP,
    )
    user_group = Group(
        name="user",
        permissions=["user"],
        org=default_org,
        type=Group.REGULAR_GROUP,
    )
    draft_group = Group(
        name="draft",
        permissions=["draft"],
        org=default_org,
        type=Group.REGULAR_GROUP,
    )
    db.session.add_all([default_org, admin_group, default_group,audit_group,seal_group,record_group,user_group,draft_group])
    db.session.commit()

    user = User(
        org=default_org,
        name=user_name,
        code=code,
        email=email,
        mobile=mobile,
        group_ids=[admin_group.id, default_group.id],
    )
    user.hash_password(password)

    db.session.add(user)
    db.session.commit()
##############20210102审批状态########################
    workflow_state_agree = Workflow_state(
        userid=user.id,
        org=default_org,
        name="批准",
    )
    workflow_state_reject = Workflow_state(
        userid=user.id,
        org=default_org,
        name="驳回",
    )
    db.session.add_all([workflow_state_agree, workflow_state_reject])
    db.session.commit()
##############20210102枚举########################
    strsql="""insert into public.enums(code,name,is_builtin,items,org_id) 
         select   'calendar_enum','时间结构类型',true,array[
        '{"key":"nature","value":"nature","label":"自然日历","title":"nature 自然日历"}'::json,
        '{"key":"account","value":"account","label":"会计日历","title":"account 会计日历"}'::json,
        '{"key":"work","value":"work","label":"生产日历","title":"work 生产日历"}'::json],""" + str(user.org_id) +""" 
        union all
        select   'timeserialnode_enum','时间节点类型',true,array[
        '{"key":"year","value":"year","label":"自然年","title":"year 自然年"}'::json,
        '{"key":"half","value":"half","label":"半年","title":"half 半年"}'::json,
        '{"key":"quarter","value":"quarter","label":"自然季度","title":"quarter 自然季度"}'::json,
        '{"key":"month","value":"month","label":"自然月","title":"month 自然月"}'::json,
        '{"key":"week","value":"week","label":"自然周","title":"week 自然周"}'::json,
        '{"key":"day","value":"day","label":"自然周","title":"day 自然日"}'::json,
        '{"key":"daterange","value":"daterange","label":"日期范围","title":"daterange 日期范围"}'::json,
        '{"key":"datetimerange","value":"datetimerange","label":"日期时间范围","title":"datetimerange 日期时间范围"}'::json],""" + str(user.org_id) +""" 
        union all
        select  'timeserialstatus_enum','时间节点状态',true,array[
        '{"key":"draft","value":"draft","label":"未打开","title":"draft 未打开"}'::json,
        '{"key":"active","value":"active","label":"打开","title":"active 打开"}'::json,
        '{"key":"freeze","value":"freeze","label":"冻结","title":"freeze 冻结"}'::json,
        '{"key":"publish","value":"publish","label":"发布","title":"publish 发布"}'::json,
        '{"key":"archive","value":"archive","label":"归档","title":"archive 归档"}'::json],""" + str(user.org_id) +"""
        union all
        select   'orgunit_enum','部门类型',true,array[
        '{"key":"enterprise","value":"enterprise","label":"企业集团","title":"enterprise 企业集团"}'::json,
        '{"key":"company","value":"company","label":"公司","title":"company 公司"}'::json,
        '{"key":"branch","value":"branch","label":"分子公司","title":"branch 分子公司"}'::json,
        '{"key":"group","value":"group","label":"事业部","title":"group 事业部"}'::json,
        '{"key":"dept","value":"dept","label":"部门","title":"dept 部门"}'::json,
        '{"key":"team","value":"team","label":"班组","title":"team 班组"}'::json,
        '{"key":"admin","value":"admin","label":"管理部门","title":"admin 管理部门"}'::json,
        '{"key":"build","value":"build","label":"生产部门","title":"build 生产部门"}'::json,
        '{"key":"sale","value":"sale","label":"销售部门","title":"sale 销售部门"}'::json,
        '{"key":"logic","value":"logic","label":"物流部门","title":"logic 物流部门"}'::json],""" + str(user.org_id) +"""
        union all
        select  'orgstruct_enum','部门类型',true,array[
        '{"key":"admin","value":"admin","label":"行政组织","title":"admin 行政组织"}'::json,
        '{"key":"hr","value":"hr","label":"人事组织","title":"hr 人事组织"}'::json,
        '{"key":"build","value":"build","label":"生产组织","title":"build 生产组织"}'::json],""" + str(user.org_id) +"""
        union all
        select   'menustyle_enum','菜单样式',true,array[
        '{"key":"horizontal ","value":"horizontal","label":"顶部水平菜单","title":"horizontal 顶部水平菜单"}'::json,
        '{"key":"vertical","value":"vertical","label":"左侧垂直菜单","title":"vertical 左侧垂直菜单"}'::json],""" + str(user.org_id) +"""
        union all
        select   'bill_enum','单据类型',true,array['{"key":"contractapply","value":"contractapply","label":"合同申请单","title":"contractapply 合同申请单"}'::json,
        '{"key":"contracts","value":"contracts","label":"合同","title":"contracts 合同"}'::json,
        '{"key":"contractseal","value":"contractseal","label":"合同用印申请单","title":"contractseal 合同用印申请单"}'::json,
        '{"key":"contractrecord","value":"contractrecord","label":"合同备案申请单","title":"contractrecord 合同备案申请单"}'::json,
        '{"key":"contractchange","value":"contractchange","label":"合同变更申请单","title":"contractchange 合同变更申请单"}'::json,
        '{"key":"contractprogress","value":"contractprogress","label":"合同违约登记单","title":"contractprogress 合同违约登记单"}'::json,
        '{"key":"contractstop","value":"contractstop","label":"合同终止申请单","title":"contractstop 合同终止申请单"}'::json,
        '{"key":"contractfinish","value":"contractfinish","label":"合同验收申请单","title":"contractfinish 合同验收申请单"}'::json,
        '{"key":"contractsettle","value":"contractsettle","label":"合同付款单","title":"contractsettle 合同付款单"}'::json],""" + str(user.org_id) +"""
        union all
        select   'amount_enum','款项类型',true,array['{"key":"yfk","value":"yfk","label":"预付款","title":"yfk 预付款"}'::json,
        '{"key":"jdk","value":"jdk","label":"进度款","title":"jdk 进度款"}'::json,
        '{"key":"wk","value":"wk","label":"尾款","title":"wk 尾款"}'::json,
        '{"key":"ysk","value":"ysk","label":"预收款","title":"ysk 预收款"}'::json],""" + str(user.org_id) +"""
        union all
        select   'contract_enum','合同类别',true,
        array['{"key":"sheb","value":"sheb","label":"设备","title":"设备","children":[{"key":"yizb1","value":"yizb1","label":"医装部","title":"医装部"},
        {"key":"houbb1","value":"houbb1","label":"后保部","title":"后保部"},{"key":"jijb1","value":"jijb1","label":"基建部","title":"基建部"}]}'::json,
        '{"key":"gongc","value":"gongc","label":"工程","title":"工程","children":[{"key":"houbb2","value":"houbb2","label":"后保部","title":"后保部"},
        {"key":"jijb2","value":"jijb2","label":"基建部","title":"基建部"}]}'::json,
        '{"key":"gongcfw","value":"gongcfw","label":"工程服务","title":"工程服务","children":[{"key":"houbb3","value":"houbb3","label":"后保部","title":"后保部"},
        {"key":"jijb3","value":"jijb3","label":"基建部","title":"基建部"}]}'::json,
        '{"key":"houqwz","value":"houqwz","label":"后勤物资","title":"后勤物资","children":[{"key":"houbb4","value":"houbb4","label":"后保部","title":"后保部"}]}'::json,
        '{"key":"yilwz","value":"yilwz","label":"医疗物资","title":"医疗物资","children":[{"key":"yizb5","value":"yizb5","label":"医装部","title":"医装部"}]}'::json,
        '{"key":"yaop","value":"yaop","label":"药品","title":"药品","children":[{"key":"yaojk6","value":"yaojk6","label":"药剂科","title":"药剂科"}]}'::json,
        '{"key":"xiaodp","value":"xiaodp","label":"消毒品","title":"消毒品","children":[{"key":"yaojk7","value":"yaojk7","label":"药剂科","title":"药剂科"}]}'::json,
        '{"key":"shij","value":"shij","label":"试剂","title":"试剂","children":[{"key":"yizb8","value":"yizb8","label":"医装部","title":"医装部"}]}'::json,
        '{"key":"yiyhc","value":"yiyhc","label":"医用耗材","title":"医用耗材","children":[{"key":"yizb9","value":"yizb9","label":"医装部","title":"医装部"}]}'::json,
        '{"key":"houqfw","value":"houqfw","label":"后勤服务","title":"后勤服务","children":[{"key":"houbb10","value":"houbb10","label":"后保部","title":"后保部"}]}'::json,
        '{"key":"shenjfw","value":"shenjfw","label":"审计服务","title":"审计服务","children":[{"key":"shenjb11","value":"shenjb11","label":"审计部","title":"审计部"}]}'::json,
        '{"key":"caiwfw","value":"caiwfw","label":"财务服务","title":"财务服务","children":[{"key":"caiwb12","value":"caiwb12","label":"财务部","title":"财务部"}]}'::json,
        '{"key":"chengz","value":"chengz","label":"承租","title":"承租","children":[{"key":"houbb13","value":"houbb13","label":"后保部","title":"后保部"}]}'::json,
        '{"key":"chuz","value":"chuz","label":"出租","title":"出租","children":[{"key":"houbb14","value":"houbb14","label":"后保部","title":"后保部"}]}'::json,
        '{"key":"xuanc","value":"xuanc","label":"宣传","title":"宣传","children":[{"key":"yuanwb15","value":"yuanwb15","label":"院务部","title":"院务部"}]}'::json,
        '{"key":"peix","value":"peix","label":"培训","title":"培训","children":[{"key":"kejb16","value":"kejb16","label":"科教部","title":"科教部"}]}'::json,
        '{"key":"gonghcg","value":"gonghcg","label":"工会采购","title":"工会采购","children":[{"key":"gongh17","value":"gongh17","label":"工会","title":"工会"}]}'::json,
        '{"key":"weix","value":"weix","label":"维修","title":"维修","children":[{"key":"yizb18","value":"yizb18","label":"医装部","title":"医装部"},
        {"key":"houbb18","value":"houbb18","label":"后保部","title":"后保部"},{"key":"jijb18","value":"jijb18","label":"基建部","title":"基建部"}]}'::json,
        '{"key":"weib","value":"weib","label":"维保","title":"维保","children":[{"key":"yizb19","value":"yizb19","label":"医装部","title":"医装部"},
        {"key":"houbb19","value":"houbb19","label":"后保部","title":"后保部"},{"key":"jijb19","value":"jijb19","label":"基建部","title":"基建部"}]}'::json,
        '{"key":"qit","value":"qit","label":"其他","title":"其他","children":[{"key":"qitbm20","value":"qitbm20","label":"其他部门","title":"其他部门"}]}'::json],""" + str(user.org_id) +"""
        union all
        select   'amounttype','资金类别',array['{"key":"自有资金","value":"自有资金","label":"自有资金","title":"自有资金"}'::json,
        '{"key":"财政资金","value":"财政资金","label":"财政资金","title":"财政资金"}'::json,
        '{"key":"财政性资金","value":"财政性资金","label":"财政性资金","title":"财政性资金"}'::json,
        '{"key":"工会资金","value":"工会资金","label":"工会资金","title":"工会资金"}'::json,
        '{"key":"科研资金","value":"科研资金","label":"科研资金","title":"科研资金"}'::json,
        '{"key":"党政资金","value":"党政资金","label":"党政资金","title":"党政资金"}'::json],true,"""+ str(user.org_id)
    db.session.execute(strsql)
    db.session.commit()
##############20210102表类型########################
    table_contractapply = Tabletype(
        code="contractapply",
        name='合同申请单',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )
    table_contracts = Tabletype(
        code="contracts",
        name='合同',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )
    table_contractseal = Tabletype(
        code="contractseal",
        name='合同用印申请单',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )
    table_contractrecord = Tabletype(
        code="contractrecord",
        name='合同备案申请单',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )
    table_contractchange = Tabletype(
        code="contractchange",
        name='合同变更申请单',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )
    table_contractprogress = Tabletype(
        code="contractprogress",
        name='合同违约登记单',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )
    table_contractstop = Tabletype(
        code="contractstop",
        name='合同终止申请单',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )
    table_contractfinish = Tabletype(
        code="contractfinish",
        name='合同验收申请单',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )
    table_contractsettle = Tabletype(
        code="contractsettle",
        name='合同付款单',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )  
    table_customertemp = Tabletype(
        code="customertemp",
        name='合同单位',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )   
    table_templates = Tabletype(
        code="templates",
        name='合同模板',
        numhead='',
        numdate='YYYYMM',
        org=default_org,
        numfoot="1",
        footlenth="4",
    )   
    db.session.add_all([table_contractapply,table_contracts, table_contractseal,table_contractrecord,table_contractchange, table_contractprogress, table_contractstop, table_contractfinish, table_contractsettle,table_customertemp,table_templates])
    db.session.commit()
##############20210102空白模板########################
    nvl_templates = Templates(
        userid=user.id,
        code="1",
        name="空白模板",
        type=["kbmb"],
        template="""<div><textarea rows="10" cols="100" textAlignVertical="top" height="100px" value="" name ="contracttemplate0" id="contracttemplate0" class="contracttemplate" ></textarea></div>""",
        org=default_org,
        is_active=True,
    )
    db.session.add(nvl_templates)
    db.session.commit()
##############20210102全局变量########################
    set_amountview = GlobalSetting(
        userid=user.id,
        code="时间线显示日期",
        type="合同主页面",
        value="1",
        message="1_显示,0_不显示",
        org=default_org,
    )
    set_mainview = GlobalSetting(
        userid=user.id,
        code="视图ID",
        type="主页",
        value="0,0",
        message="查询ID，视图ID",
        org=default_org,
    )
    set_contractview = GlobalSetting(
        userid=user.id,
        code="款项ID",
        type="合同主页面",
        value="0,0",
        message="查询ID，视图ID",
        org=default_org,
    )
    set_contractyusuan = GlobalSetting(
        userid=user.id,
        code="预算控制",
        type="合同页面",
        value="",
        message="[{合同类别编码,部门ID,输入,检查}]  输入(0_可空,1_必填,2_不可用),检查(0_不检查,1_提示,2_控制)",
        org=default_org,
    )
    db.session.add_all([set_amountview, set_mainview,set_contractview,set_contractyusuan])
    db.session.commit()
##############20210102添加视图和函数########################
##########################################1outlaybudgetview
    strsql="""CREATE OR REPLACE VIEW public.outlaybudgetview AS 
select t1.*,COALESCE(t2.contractamount,0) as contractamount,COALESCE(t1.amount-t2.contractamount,0) as surplusamount
from 
(select 1 as id,'10000001'::varchar as code,'预算编号1_50000'::varchar as name,50000 as amount,id as org_id from public.organizations
 union 
select 2 as id,'10000002'::varchar as code,'预算编号2_20000'::varchar as name,20000 as amount,id as org_id  from public.organizations) t1
left join (select budgetcode,org_id,sum(amount) as contractamount From public.contracts where status=2 group by  budgetcode,org_id) t2
on t1.code=t2.budgetcode and t1.org_id=t2.org_id;"""
    db.session.execute(strsql)
    db.session.commit()
    # os.system("sqlcmd.exe -S localhost -U frappe -P frappe -I:'/home/yhy/dazcontractnew/setup/函数/alertsadd.sql'")
##########################################################2alertsadd
    strsql="""CREATE OR REPLACE FUNCTION public.alertsadd(
    p_bill varchar,---表名
    p_billid integer,----表ID
    p_status integer,---审核后状态 0反审核,1--提交审核,2审核
    p_workflow_transitionid integer,--流程动作ID
    p_tijiao integer,----1合同提交时是否提醒
    p_userid integer,---用户ID
    p_message varchar,
    p_org_id integer
    )
  RETURNS boolean  AS
$BODY$
declare  
    v_alertsid  integer;
    v_query_id  integer;
    v_billname character varying(255);
    v_billcode character varying(255);
    v_timelimit integer;
    v_timemsg character varying(255);
begin
	select nextval('public.alerts_id_seq') into v_alertsid;
	select COALESCE(min(id),0) into v_query_id From public.queries;
	select code,name 
	into  v_billcode,v_billname
	From 
	(select 'contractapply' as tablename,id,code,'合同申请单' as name from  public.contractapply union all
	 select 'contracts' as tablename,id,code,'合同' as name from  public.contracts union all
	 select 'contractseal' as tablename,id,code,'合同用印申请单' as name from  public.contractseal union all
	 select 'contractrecord' as tablename,id,code,'合同备案申请单' as name from  public.contractrecord  union all
	 select 'contractchange' as tablename,id,code,'合同变更申请单' as name from  public.contractchange union all
	 select 'contractprogress' as tablename,id,code,'合同违约登记单' as name from  public.contractprogress  union all
	 select 'contractsettle' as tablename,id,code,'合同付款单' as name from  public.contractsettle union all
	select 'contractstop' as tablename,id,code,'合同终止申请单' as name from  public.contractstop union all
	select 'contractfinish' as tablename,id,code,'合同验收申请单' as name from  public.contractfinish) AB
	where tablename=p_bill and id=p_billid;
  if v_query_id=0 then
	return false;
  end if;
  if p_tijiao=1 then
      select COALESCE(t2.timelimit,0)
      into v_timelimit
      From public.workflow_transition t1 inner join public.workflow_bill_state t2 on t1.workflowid=t2.workflowid and t1.workflow_stateid=t2.workflow_stateid
      where t1.id=p_workflow_transitionid;
  else
      select COALESCE(t2.timelimit,0)
      into v_timelimit
      From public.workflow_transition t1 inner join public.workflow_bill_state t2 on t1.workflowid=t2.workflowid and t1.next_workflow_stateid=t2.workflow_stateid
      where t1.id=p_workflow_transitionid;
  end if;
  v_timemsg :='';
  if v_timelimit>0 then
       v_timemsg :='限在' || cast(v_timelimit as VARCHAR) || '天内审核';
  end if;
  if p_status =1 then
	  insert into public.notification_destinations(
	  id,org_id,user_id,name,type,options,
	  created_at)
	  select  nextval('public.notification_destinations_id_seq'),p_org_id,id,email,'email','{"addresses": "' || name || '"}',
	  now() from public.users where id in(select t2.id From public.workflow_transition t1 , public.users t2 
	   where ((t2.id = any(t1.allow_user)) or (t1.allow_group && t2.groups)) and t1.id=p_workflow_transitionid)
	  and id not in(select user_id from public.notification_destinations);
	  
	--raise notice 'notification_destinations: %,%',v_orgid,p_workflow_transitionid;
	  
	 insert into public.alerts(
	  updated_at,created_at,id,name,
	  contract_id,user_id,options,
	  state,query_id)
	  select now(),now(),v_alertsid,v_billname || ':' || v_billcode || (case when p_workflow_transitionid=0 then '已提交' else '待审核' end) || v_timemsg,
	  p_billid,p_userid,'{"op":">","value":1,"muted":false,"column":"'|| p_billid || '","custom_subject":"' || v_billname || '","custom_body":"' || p_message || v_billname || ':' || v_billcode  || (case when p_workflow_transitionid=0 then '已提交' else '待审核' end) || '"}',
	  'unknown',v_query_id;
	---raise notice 'alerts: %,%,%,%,%,%,%,%',v_alertsid,v_billname,v_billcode,p_workflow_transitionid,p_billid,p_userid,p_message,v_query_id;

	---raise notice 'Parameter is: %,%',v_alertsid,p_workflow_transitionid;
  	insert into public.alert_subscriptions(
	  updated_at,created_at,user_id,destination_id,
	  alert_id)
	select now(),now(),t2.id,t1.id,v_alertsid 
        from public.notification_destinations t1 inner join public.users t2 on t1.user_id=t2.id 
	  where  t2.id in (select t2.id From public.workflow_transition t1 , public.users t2 
	   where ((t2.id = any(t1.allow_user)) or (t1.allow_group && t2.groups)) and t1.id=p_workflow_transitionid) and t2.id <>p_userid;
  elsif p_status =2 then
	  insert into public.notification_destinations(
	  id,org_id,user_id,name,type,options,
	  created_at)
	  select  nextval('public.notification_destinations_id_seq'),p_org_id,id,email,'email','{"addresses": "' || name || '"}',
	  now() from public.users where id in(select distinct userid From public.workflow_action where bill_enum=p_bill and billid=p_billid)
	  and id not in(select user_id from public.notification_destinations);
	  
	 insert into public.alerts(
	  updated_at,created_at,id,name,
	  contract_id,user_id,options,
	  state,query_id)
	  select now(),now(),v_alertsid,v_billname || ':' || v_billcode || (case when p_status=2 then '已审核' else '已驳回' end),
	  p_billid,p_userid,'{"op":">","value":1,"muted":false,"column":"'|| p_billid || '","custom_subject":"' || v_billname || '","custom_body":"' || p_message || v_billname || ':' || v_billcode  || (case when p_status=2 then '已审核' else '已驳回' end)|| '"}',
	  'unknown',v_query_id;

  	insert into public.alert_subscriptions(
	  updated_at,created_at,user_id,destination_id,
	  alert_id)
	select now(),now(),t2.id,t1.id,v_alertsid 
        from 
	public.notification_destinations t1 inner join public.users t2 on t1.user_id=t2.id 
	where  t2.id in(select distinct userid From public.workflow_action where bill_enum=p_bill and billid=p_billid)  and t2.id <>p_userid;
  end if;
   return true; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
###########################################3gaincode
    strsql="""CREATE OR REPLACE FUNCTION public.gaincode(
    p_bill character varying,
    p_contracttype character varying,
    p_field character varying,
    p_billdate timestamp with time zone,
    p_org_id integer)
  RETURNS character varying AS
$BODY$
declare
    v_numhead character varying;
    v_numfoot integer;
    v_footlenth integer;
    v_code character varying;
    v_sql text;
begin  
-----current_date
   select numhead || to_char(p_billdate,numdate) as numhead,numfoot,case when footlenth >8 then 8 else footlenth end
   into v_numhead,v_numfoot,v_footlenth
   from  public.tabletype where code=p_bill;

   v_sql := 'select COALESCE(max(' || p_field || '),''' || v_numhead || ''') from public.' || p_bill || ' where ' || p_field || ' like ''' || v_numhead || '%'' and org_id=' || p_org_id ;
   execute  v_sql into v_code; 
   if v_code = v_numhead then
      select v_numhead || to_char(v_numfoot,'fm' || substr('00000000',1,v_footlenth)) into v_code ;
   else
      select v_numhead || to_char(to_number(substr(v_code,length(v_numhead)+1), '9999999999999999999')+1,'fm' || substr('00000000',1,v_footlenth)) into v_code ;
   end if;
   RETURN v_code::character varying; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
############################################4contracttypeview
    strsql="""CREATE OR REPLACE VIEW public.contracttypeview AS 
 SELECT aa.items,aa.org_id,
    aa.items ->> 'value'::text AS typecode,
    aa.items ->> 'label'::text AS typename,
    ARRAY[((aa.items ->> 'value'::text))::character varying(255)] AS type
   FROM ( SELECT unnest(enums.items) AS items,org_id
           FROM enums
          WHERE enums.code::text = 'contract_enum'::text) aa
  WHERE (aa.items -> 'children'::text) IS NULL
UNION ALL
 SELECT bb.items,bb.org_id,
    bb.items ->> 'value'::text AS typecode,
    bb.typename || '/' || (bb.items ->> 'label'::text)  AS typename,
    ARRAY[bb.typecode::character varying(255), ((bb.items ->> 'value'::text))::character varying(255)] AS type
   FROM ( SELECT json_array_elements(aa.items -> 'children'::text) AS items,aa.org_id,
            aa.items ->> 'value'::text AS typecode,aa.items ->> 'label'::text AS typename
           FROM ( SELECT unnest(enums.items) AS items,org_id
                   FROM enums
                  WHERE enums.code::text = 'contract_enum'::text) aa) bb
  WHERE (bb.items -> 'children'::text) IS NULL
UNION ALL
 SELECT obj.items,obj.org_id,
    obj.items ->> 'value'::text AS typecode,
    obj.typename || '/' || (obj.items ->> 'label'::text) AS typename,
    ARRAY[obj.typecode::character varying(255), obj.typecode2::character varying(255), ((obj.items ->> 'value'::text))::character varying(255)] AS type
   FROM ( SELECT json_array_elements(bb.items -> 'children'::text) AS items,bb.org_id,
            bb.typecode,bb.typename || '/' || (bb.items ->> 'label'::text)  AS typename,
            bb.items ->> 'value'::text AS typecode2
           FROM ( SELECT json_array_elements(aa.items -> 'children'::text) AS items,aa.org_id,
                    aa.items ->> 'value'::text AS typecode,aa.items ->> 'label'::text AS typename
                   FROM ( SELECT unnest(enums.items) AS items,org_id
                           FROM enums
                          WHERE enums.code::text = 'contract_enum'::text) aa) bb) obj;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
############################################5overpage
    strsql="""CREATE OR REPLACE FUNCTION public.overpage(
            -- p_schema varchar ='public',
            p_tblname   varchar,       -- 表名
            p_strgetfield varchar = '*',  -- 需要返回的列 
            p_orderfield varchar='',      -- 排序的字段名
            p_pagesize   integer = 10,          -- 页尺寸
            p_pageindex  integer = 1,           -- 页码
            p_ordertype integer = 0,     -- 设置排序类型, 1降序 0升序
            p_strwhere  text = ''  -- 查询条件 (注意: 不要加 where)
            )
            RETURNS json  AS
            $BODY$
            declare  
            v_strsql text;  
            v_strwhere text;
            v_total  integer;
            v_pagecount integer;
            v_strtmp varchar(1000);
            v_strorder varchar(1000);
            v_data text;
            begin   
            if p_strwhere='' or p_strwhere is null then
                v_strwhere := ' 1=1 ';
            else
                v_strwhere := p_strwhere;
            end if;
            --以下执行总数统计,计算总数，页数
            v_strsql := 'select count(*) as Total  from ' || p_tblname || ' where ' || v_strwhere ;
            execute v_strsql into v_total;
            v_pagecount := ceil(v_total*1.0/p_pagesize);

            --以下计算返回结果集
            v_strsql := '';
            if p_ordertype != 0 then
                v_strtmp := '<(select min' ;
                v_strorder := ' order by ' || p_orderfield || ' desc';
            --如果@OrderType不是0，就执行降序，这句很重要！
            else 
                v_strtmp := '>(select max';
                v_strorder := ' order by ' || p_orderfield || ' asc';
            end if;

            if p_pageindex = 1 then   
                v_strsql := 'select ' || p_strgetfield || ' from ' || p_tblname || ' where ' || v_strwhere || ' ' || v_strorder || ' limit ' || p_pagesize;
            --如果是第一页就执行以上代码，这样会加快执行速度
            else
            --以下代码赋予了@strSQL以真正执行的SQL代码
                v_strsql := 'select ' || p_strgetfield || ' from ' || p_tblname || ' where ' || v_strwhere || ' ' 
                || v_strorder || ' limit ' || p_pagesize || ' offset ' || ((p_pageindex-1)*p_pagesize);
            --    v_strsql := 'select ' || p_strgetfield || ' from '  
            --        || p_tblname || ' where [' || p_orderfield || ']' || v_strtmp || '(['  
            --        || p_orderfield || ']) from (select top ' || ((p_pageindex-1)*p_pagesize) || ' ['  
            --        || p_orderfield || '] from '|| p_tblname || ' where ' || v_strwhere || ' '  
            --        || v_strorder || ') as tblTmp) and ' ||v_strwhere || ' ' || v_strorder ;
            end if;
            v_strsql :='select array_to_json(array_agg(row_to_json(t)))  from ('|| v_strsql ||') t';
            execute v_strsql into v_data;
            -----如果为空改为JSON数组格式
            select COALESCE(v_data, '[]') into v_data;
            v_data := '{"total":"'|| v_total ||'","pagecount":"'|| v_pagecount ||'","data":' || v_data ||'}';

            RETURN v_data::json; 
            END;  
            $BODY$
            LANGUAGE plpgsql VOLATILE
            COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
############################################6pushbill
    strsql="""CREATE OR REPLACE FUNCTION public.pushbill(
    p_sourcebill varchar,---来源表名
    p_sourcebillid integer,----来源表ID
    p_bill varchar,---表名
    p_userid integer,---用户ID
    p_typeid integer-----合同有来源单据类型0续签，1另签，2补签
    )
  RETURNS json  AS
$BODY$
declare  
    v_billid integer;
    v_sql text;
    v_data text;
    v_cnt integer;
begin
  if p_sourcebill ='contractapply' then
     if p_bill='contracts' then
	 select nextval('public.contracts_id_seq') into v_billid;
	 
	 insert into public.contracts (id,userid ,bill_date,employeeid ,departmentid,code ,
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,sourcetype,sourceid,typeid,org_id,updated_at,created_at,status) 
	 select v_billid,p_userid ,now(),employeeid ,departmentid,public.gaincode('contracts',type[array_length(type,1)],'code',now(),org_id) ,
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,'contractapply',id,p_typeid,org_id,now(),now(),0
	  from  public.contractapply where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,v_billid,'合同申请('|| code || ' ' || name ||')',bill_date,
	   bill_date,'contractapply' ,id from public.contractapply where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,v_billid,'"合同('|| code || ' ' || name ||')新建',now(),
	   now(),'contracts' ,id from public.contracts where id=v_billid;

	   insert into public.attaches(userid,type,annex,updated_at,created_at,
           sourcetype,sourceid,org_id)
           select p_userid,p_bill,'[]'::json,now(),now(),
           p_bill,v_billid,org_id from  public.contracts where id=v_billid;

          select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contracts where id=v_billid) t;
         return v_data::json; 
     end if;
  elsif p_sourcebill ='contracts' then
     if p_bill='contracts' then
	 select nextval('public.contracts_id_seq') into v_billid;
	 insert into public.contracts (id,userid ,bill_date,employeeid ,departmentid,code ,
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,sourcetype,sourceid,
	  officialcode,templateid,templatecontent,typeid,org_id,updated_at,created_at,status) 
	 select v_billid,p_userid ,now(),employeeid ,departmentid,public.gaincode('contracts',type[array_length(type,1)],'code',now(),org_id),
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,'contracts',id,
	  '',templateid,templatecontent,p_typeid,org_id,now(),now(),0
	  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,
	   case when p_typeid=0 then '"合同('|| code || ' ' || name ||')被续签' when p_typeid=1 then '"合同('|| code || ' ' || name ||')被另签' else '"合同('|| code || ' ' || name ||')被补签' end,now(),
	   now(),'contracts' ,id from public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,v_billid,
	   case when p_typeid=0 then '"合同('|| code || ' ' || name ||')续签' when p_typeid=1 then '"合同('|| code || ' ' || name ||')另签' else '"合同('|| code || ' ' || name ||')补签' end,now(),
	   now(),'contracts' ,id from public.contracts where id=v_billid;

	   insert into public.attaches(userid,type,annex,updated_at,created_at,
           sourcetype,sourceid,org_id)
           select p_userid,p_bill,'[]'::json,now(),now(),
           p_bill,v_billid,org_id from  public.contracts where id=v_billid;

           select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contracts where id=v_billid) t;
           return v_data::json; 
     elsif p_bill='contractseal' then---合同用印申请单
       select count(*) into v_cnt from public.contractseal where contractsid=p_sourcebillid;
       if v_cnt=0 then
	 select nextval('public.contractseal_id_seq') into v_billid;
	 insert into public.contractseal(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractseal','contractseal','code',now(),org_id),name,'',
	  'contracts',id,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'"合同用印('|| code || ' ' || name ||')',now(),
	   now(),p_bill ,v_billid from public.contractseal where id=v_billid;
	   
          select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractseal where id=v_billid) t;
          return v_data::json;  
        else
          select array_to_json(array_agg(row_to_json(t))) into v_data from 
          (select 1 as flag, id,'合同用印申请单已存在:' || code as code from public.contractseal where contractsid=p_sourcebillid) t;
          return v_data::json;  
        end if; 
    elsif p_bill='contractrecord' then---合同备案申请单
       select count(*) into v_cnt from public.contractrecord where contractsid=p_sourcebillid;
       if v_cnt=0 then
	 select nextval('public.contractrecord_id_seq') into v_billid;
	 insert into public.contractrecord(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractrecord','contractrecord','code',now(),org_id) ,name,'',
	  'contracts',id,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'"合同备案('|| code || ' ' || name ||')',now(),
	   now(),p_bill ,v_billid from public.contractrecord where id=v_billid;
	   
          select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractrecord where id=v_billid) t;
          return v_data::json;  
        else
          select array_to_json(array_agg(row_to_json(t))) into v_data from 
          (select 1 as flag, id,'合同备案申请单已存在:' || code as code from public.contractrecord where contractsid=p_sourcebillid) t;
          return v_data::json;  
        end if; 
    elsif p_bill='contractchange' then---合同变更申请单
	 select nextval('public.contractchange_id_seq') into v_billid;
	 insert into public.contractchange(id,userid ,bill_date,contractsid,employeeid ,departmentid,code ,
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,sourcetype,sourceid,contractcode,changebefore,
	  officialcode,templateid,templatecontent,org_id,updated_at,created_at) 
	 select v_billid,p_userid ,now(),id ,employeeid ,departmentid,public.gaincode('contractchange','contractchange','code',now(),org_id),
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,'contracts',id,code,
	  (select array_to_json(array_agg(row_to_json(t)))  from (select* from  public.contracts where id=p_sourcebillid) t),
	  officialcode,templateid,templatecontent,org_id,now(),now()
	  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'"合同变更('|| code || ' ' || name ||')',now(),
	   now(),p_bill ,v_billid from public.contractchange where id=v_billid;
	    
           select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractchange where id=v_billid) t;
           return v_data::json;
    elsif p_bill='contractprogress' then---合同违约登记单
	 select nextval('public.contractprogress_id_seq') into v_billid;
	 insert into public.contractprogress(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,percent,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractprogress','contractprogress','code',now(),org_id),name,'',
	  'contracts',id,0,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'"合同违约登记('|| code || ' ' || name ||')',now(),
	   now(),p_bill ,v_billid from public.contractprogress where id=v_billid;

             select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractprogress where id=v_billid) t;
           return v_data::json;
    elsif p_bill='contractstop' then---合同终止申请单
        select count(*) into v_cnt
        from (select id From contractstop where contractsid=p_sourcebillid
        union 
       select id From contractfinish where contractsid=p_sourcebillid) t1;
       if v_cnt=0 then
	 select nextval('public.contractstop_id_seq') into v_billid;
	 insert into public.contractstop(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractstop','contractstop','code',now(),org_id) ,name,'',
	  'contracts',id,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;
	  
	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'"合同终止('|| code || ' ' || name ||')',now(),
	   now(),p_bill ,v_billid from public.contractstop where id=v_billid;

           select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractstop where id=v_billid) t;
          return v_data::json;
        else
          select array_to_json(array_agg(row_to_json(t))) into v_data from 
          (select 1 as flag,1 as id,'合同终止申请单已存在:' || code as code  From contractstop where contractsid=p_sourcebillid
           union select 1 as flag,1 as id,'合同验收申请单已存在:' || code as code  From contractfinish where contractsid=p_sourcebillid) t;
          return v_data::json;  
        end if; 
    elsif p_bill='contractfinish' then---合同验收申请单
        select count(*) into v_cnt
        from (select id From contractstop where contractsid=p_sourcebillid
        union 
       select id From contractfinish where contractsid=p_sourcebillid) t1;
       if v_cnt=0 then
	 select nextval('public.contractfinish_id_seq') into v_billid;
	 insert into public.contractfinish(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractfinish','contractfinish','code',now(),org_id) ,name,'',
	  'contracts',id,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'"合同验收('|| code || ' ' || name ||')',now(),
	   now(),p_bill ,v_billid from public.contractfinish where id=v_billid;

            select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractfinish where id=v_billid) t;
           return v_data::json;
        else
          select array_to_json(array_agg(row_to_json(t))) into v_data from 
          (select 1 as flag,1 as id,'合同终止申请单已存在:' || code as code  From contractstop where contractsid=p_sourcebillid
           union select 1 as flag,1 as id,'合同验收申请单已存在:' || code as code  From contractfinish where contractsid=p_sourcebillid) t;
          return v_data::json;  
        end if; 
    elsif p_bill='contractsettle' then---合同付款单
	 select nextval('public.contractsettle_id_seq') into v_billid;
	 insert into public.contractsettle(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,percent,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractsettle','contractsettle','code',now(),org_id) ,name,'',
	  'contracts',id,0,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'"合同付款('|| code || ' ' || name ||')',now(),
	   now(),p_bill ,v_billid from public.contractsettle where id=v_billid;

          select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractsettle where id=v_billid) t;
         return v_data::json;
    end if;
  end if;
  select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,0 as id,'没有找到相关单据' code) t;
  return v_data::json;
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
############################################7timeserial_treedata
    strsql="""CREATE OR REPLACE FUNCTION public.timeserial_treedata(
    v_type integer,
    v_timestruct_parent_id integer,
    v_parent_id integer,
    v_timeserialstatus_enum varchar,
    p_org_id integer)
  RETURNS json  AS
$BODY$
declare  
    v_datatree text;  
    v_cnt integer;
 v_timestructid integer;
 v_timeserialid integer;
 v_calendar_enum varchar(50);
 v_name varchar(50);
 v_min	integer;
 v_max	integer;
 v_timeserialmin	integer;
 v_timeserialmax	integer;
 --timestruct_refcursor CURSOR(p_timestruct_parent_id INTEGER) FOR select id,calendar_enum,name From timestruct where parent_id=p_timestruct_parent_id;
 --timeserial_refcursor CURSOR(p_parent_id INTEGER,p_timestructid integer) FOR 
 --  select id,cast(num as VARCHAR) || name  as title From timeserial where parent_id=p_parent_id and timestruct_id=p_timestructid;
begin   
   v_datatree ='[';
  if v_type=0 then
    select COALESCE(min(id),0),COALESCE(max(id),0),count(*)
    into v_min,v_max,v_cnt From timestruct where org_id =p_org_id and parent_id=v_timestruct_parent_id;
    if v_min>0 then
       while v_min <= v_max loop
         select id,calendar_enum,name 
         into v_timestructid,v_calendar_enum,v_name
         From timestruct where id=v_min;
	 if v_cnt=1 and v_timestruct_parent_id>0 then
-------------------------
            select COALESCE(min(id),0),COALESCE(max(id),0)
            into v_timeserialmin,v_timeserialmax
            From timeserial where parent_id=v_parent_id and timestruct_id=v_timestructid
            and (regexp_split_to_array (timeserialstatus_enum,','))::varchar[] <@ regexp_split_to_array (v_timeserialstatus_enum,',')::varchar[];
	    if v_timeserialmin>0 then
              while v_timeserialmin <= v_timeserialmax loop
                select id,cast(num as VARCHAR) || name  as title 
		into v_timeserialid,v_name		
                From timeserial where id=v_timeserialmin;
		if v_datatree='[' then
		      v_datatree = v_datatree || '{"title":"'|| v_name ||'","value":"'|| v_timeserialid ||'","children":' || timeserial_treedata(0,v_timestructid,v_timeserialid,v_timeserialstatus_enum) ||'}';
		else
		      v_datatree = v_datatree || ',{"title":"'|| v_name ||'","value":"'|| v_timeserialid ||'","children":' || timeserial_treedata(0,v_timestructid,v_timeserialid,v_timeserialstatus_enum) ||'}';
		end if;
		if v_timeserialmin < v_timeserialmax then
                  select COALESCE(min(id),0)
	          into v_timeserialmin
                  From timeserial where parent_id=v_parent_id and timestruct_id=v_timestructid and id>v_timeserialmin
                  and (regexp_split_to_array (timeserialstatus_enum,','))::varchar[] <@ regexp_split_to_array (v_timeserialstatus_enum,',')::varchar[];
                else
                  v_timeserialmin =v_timeserialmax+1;
                end if;
              end loop; 
	    end if; 
-------------------------
	  else
	    if v_datatree='[' then
	      v_datatree = v_datatree || '{"title":"'|| v_name ||'","value":"'|| v_calendar_enum ||'","disabled":true,"children":' || timeserial_treedata(1,v_timestructid,v_parent_id,v_timeserialstatus_enum) ||'}';
	    else
	      v_datatree = v_datatree || ',{"title":"'|| v_name ||'","value":"'|| v_calendar_enum ||'","disabled":true,"children":' || timeserial_treedata(1,v_timestructid,v_parent_id,v_timeserialstatus_enum) ||'}';
	    end if;
	  end if;
          if v_min < v_max then
            select COALESCE(min(id),0)
	    into v_min
            from timestruct  where org_id =p_org_id and parent_id=v_timestruct_parent_id and id>v_min;
          else
            v_min =v_max+1;
          end if;
        end loop; 
      end if;
   else
      select COALESCE(min(id),0),COALESCE(max(id),0)
      into v_timeserialmin,v_timeserialmax
      From timeserial where parent_id=v_parent_id and timestruct_id=v_timestruct_parent_id
      and (regexp_split_to_array (timeserialstatus_enum,','))::varchar[] <@ regexp_split_to_array (v_timeserialstatus_enum,',')::varchar[];
      if v_timeserialmin>0 then
        while v_timeserialmin <= v_timeserialmax loop
          select id,cast(num as VARCHAR) || name  as title 
	  into v_timeserialid,v_name		
          From timeserial where id=v_timeserialmin;
	  if v_datatree='[' then
	    v_datatree = v_datatree || '{"title":"'|| v_name ||'","value":"'|| v_timeserialid ||'","children":' || timeserial_treedata(0,v_timestruct_parent_id,v_timeserialid,v_timeserialstatus_enum) ||'}';
	  else
	    v_datatree = v_datatree || ',{"title":"'|| v_name ||'","value":"'|| v_timeserialid ||'","children":' || timeserial_treedata(0,v_timestruct_parent_id,v_timeserialid,v_timeserialstatus_enum) ||'}';
	  end if;
	  if v_timeserialmin < v_timeserialmax then
            select COALESCE(min(id),0)
	    into v_timeserialmin
            From timeserial where parent_id=v_parent_id and timestruct_id=v_timestruct_parent_id and id>v_timeserialmin
            and (regexp_split_to_array (timeserialstatus_enum,','))::varchar[] <@ regexp_split_to_array (v_timeserialstatus_enum,',')::varchar[];
          else
            v_timeserialmin =v_timeserialmax+1;
          end if;
        end loop; 
      end if; 
    end if;  
     v_datatree = v_datatree || ']';
   RETURN v_datatree::json; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
############################################8timeserial_tree
    strsql="""CREATE OR REPLACE FUNCTION public.timeserial_tree(
    v_calendar_enum varchar,
    p_org_id integer)
  RETURNS json  AS
$BODY$
declare  
    v_timeserialstatus_enum varchar(100);
    v_timestructid  integer;
    v_type integer;
    v_datatree	json;
begin
   select COALESCE(min(id),0)
   into v_timestructid
   From timestruct where org_id=p_org_id and calendar_enum = v_calendar_enum  and parent_id=0;
   if v_timestructid=0 then---如果时间序列结构ID为0，只显示已打开，否则显示打开和已关闭
     v_timeserialstatus_enum ='active';
     v_type =0;
   else
     v_timeserialstatus_enum ='active,freeze,publish';
     v_type=1;
   end if;
   v_datatree = timeserial_treedata(v_type,v_timestructid,0,v_timeserialstatus_enum,p_org_id);
   RETURN v_datatree; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
############################################9workflowchange
    strsql="""CREATE OR REPLACE FUNCTION public.workflowchange(
    p_bill varchar,---表名
    p_billid integer,----表ID
    p_status integer,---审核后状态 0反审核,1--提交审核,2审核
    p_type integer,--0新增，1审核，2驳回
    p_userid integer---用户ID
    )
  RETURNS boolean  AS
$BODY$
declare  
    v_sourcetype varchar(100);
    v_sourceid  integer;
    v_cnt integer;
    v_sql text;
    v_data text;
    v_message varchar(200);
    
begin
  if p_status =2 then
    ---if p_bill='contractapply' then ---合同申请单
    if p_bill='contracts' then---合同
        update contracts set officialcode= public.gaincode('contracts',type[array_length(type,1)],'officialcode',bill_date,org_id)
       where id=p_billid and typeid<>1;
    elseif p_bill='contractseal' then---合同用印申请单
       update contracts set contractsealid= p_billid
       where id in(select contractsid from public.contractseal where id=p_billid);
    elsif p_bill='contractrecord' then---合同备案申请单
       update contracts set contractrecordid= p_billid
       where id in(select contractsid from public.contractrecord where id=p_billid);
    elsif p_bill='contractchange' then---合同变更申请单
	update public.contracts set   employeeid =t1.employeeid,  departmentid=t1.departmentid,
	code =t1.contractcode,  name =t1.name,
	type =t1.type,  party_aid =t1.party_aid,
	party_bid =t1.party_bid,  party_cid =t1.party_cid,
	pay_direction =t1.pay_direction,  sign_date =t1.sign_date,pricetype=t1.pricetype,
	start_date =t1.start_date,  end_date =t1.end_date,
	amount =t1.amount,  amount_words =t1.amount_words,
	officialcode =t1.officialcode,  templateid =t1.templateid,
	templatecontent =t1.templatecontent,  message =t1.message
	from public.contractchange t1 where t1.id=p_billid;
  ---  elsif p_bill='contractprogress' then---合同违约登记单

    elsif p_bill='contractstop' then---合同终止申请单
    	update public.contracts set   billstatusid =3
	 where id in(select contractsid from public.contractstop where id=p_billid);
    elsif p_bill='contractfinish' then---合同验收申请单
    	update public.contracts set   billstatusid =2
	 where id in(select contractsid from public.contractfinish where id=p_billid);
  ---  elsif p_bill='contractsettle' then---合同付款单

    end if;
  else
    if p_bill='contractseal' then---合同用印申请单
       update contracts set contractsealid= 0
       where id in(select contractsid from public.contractseal where id=p_billid);
    elsif p_bill='contractrecord' then---合同备案申请单
       update contracts set contractrecordid= 0
       where id in(select contractsid from public.contractrecord where id=p_billid);
  ---  elsif p_bill='contractprogress' then---合同违约登记单

    elsif p_bill='contractstop' then---合同终止申请单
    	update public.contracts set   billstatusid =0
	 where id in(select contractsid from public.contractstop where id=p_billid);
    elsif p_bill='contractfinish' then---合同验收申请单
    	update public.contracts set   billstatusid =0
	 where id in(select contractsid from public.contractfinish where id=p_billid);
  ---  elsif p_bill='contractsettle' then---合同付款单

    end if;
  end if;
   return true; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
############################################10workflowcheck
    strsql="""CREATE OR REPLACE FUNCTION public.workflowcheck(
    p_bill character varying,
    p_billid integer,
    p_workflow_action_masterid integer,
    p_message text,
    p_userid integer,
    p_billdate timestamp with time zone,
    p_altermsg varchar,
    p_org_id integer)
  RETURNS json AS
$BODY$
declare  
    v_workflowid integer;  ----工作流程ID
    v_workflow_state_field   varchar(50);--流程状态字段
    v_workflow_stateid  integer;  ----工作流程状态ID
    v_next_workflow_stateid  integer;  ----下一工作流程状态ID
    v_status integer;---审批后但据状态
    v_workflow_action_mastername varchar(255);--动作名称
    v_workflow_transitionid integer;---工作流程
    v_billuserid integer;
    v_cnt integer;
    v_sql text;
    v_data text;
    
 /*v_timestructid integer;
 v_timeserialid integer;
 v_calendar_enum varchar(50);
 v_name varchar(50);
 v_min	integer;
 v_max	integer;
 v_timeserialmin	integer;
 v_timeserialmax	integer;*/
 --timestruct_refcursor CURSOR(p_timestruct_parent_id INTEGER) FOR select id,calendar_enum,name From timestruct where parent_id=p_timestruct_parent_id;
 --timeserial_refcursor CURSOR(p_parent_id INTEGER,p_timestructid integer) FOR 
 --  select id,cast(num as VARCHAR) || name  as title From timeserial where parent_id=p_parent_id and timestruct_id=p_timestructid;
begin
      v_sql := 'select status From ' || p_bill || ' where id=' || p_billid;
      execute  v_sql into v_status; ---查找状态
      if p_workflow_action_masterid=1 then
         if v_status=2 then
             select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'单据已审批！' as message ) t;
             return v_data::json; 
         end if;
      else
         if v_status=0 then
             select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'单据已取消审批！' as message ) t;
             return v_data::json; 
         end if;
      end if;        
      v_sql := 'select userid From ' || p_bill || ' where id=' || p_billid;
      execute  v_sql into v_billuserid; ---查找制单人ID
   select count(id)
   into v_cnt
   From workflow where org_id =p_org_id and is_active=true and bill_enum=p_bill and (orgunitid=0 or orgunitid in(public.workflowtabsel(p_bill,'','departmentid','id',p_billid)));
   if v_cnt=0 then--没有审批流
      -------------------------------------检查权限
      if v_billuserid != p_userid then
         SELECT count(users.id) into v_cnt
         FROM users,groups 
         WHERE users.id=p_userid and groups.id = ANY (users.groups) and 'admin'=ANY(groups.permissions);
         if v_cnt=0 then
             select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'没有权限，只有制单人或系统管理员可以审批' as message ) t;
             return v_data::json; 
         end if;
      end if;
        if p_workflow_action_masterid=1 then
            v_status :=2;
        else
            v_status :=0;
        end if;
        ---------------------
      if public.alertsadd(p_bill,p_billid,v_status,0,1,p_userid,p_altermsg,p_org_id) then ---添加提醒功能
      end if;
     ---------------------------------------------------- 
     ------------开始提交审核-----------
     if p_bill='contracts' then
	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_billid,case when v_status=2 then '已审核('|| code || ' ' || name ||')' else '已驳回('|| code || ' ' || name ||')' end,now(),
	   now(),'contracts' ,id from public.contracts where id=p_billid;
     end if;
     ---------------------------------
     if public.workflowchange(p_bill,p_billid,v_status,p_workflow_action_masterid,p_userid) then
	      v_sql := 'update ' || p_bill || ' set status=' || v_status || ' where id=' || p_billid;
	      execute  v_sql; 
	      select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,'审核成功' as message ) t;
	       return v_data::json; 
      else
	      select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'审核失败(workflowchange)' as message ) t;
	       return v_data::json; 
      end if;

   end if;
   if v_cnt>1 then--活动审批流有多个
      select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'活动审批流有多个,审核失败' as message ) t;
       return v_data::json; 
   end if;
   ----------------------查询动作名称
   select name into v_workflow_action_mastername
   From public.workflow_action_master where id=p_workflow_action_masterid;
   -------------------------------
   select id
   into v_workflowid
   From workflow where org_id =p_org_id and is_active=true and bill_enum=p_bill  and (orgunitid=0 or orgunitid in(public.workflowtabsel(p_bill,'','departmentid','id',p_billid)));
   select workflow_state_field
   into v_workflow_state_field
   From workflow where org_id =p_org_id and is_active=true and bill_enum=p_bill  and (orgunitid=0 or orgunitid in(public.workflowtabsel(p_bill,'','departmentid','id',p_billid)));
   -----判断是否有保存工作流状态列，如果没有，就添加
   select count(column_name) 
   into v_cnt
   from information_schema.columns 
   where table_schema='public' and table_name=p_bill and column_name=v_workflow_state_field;
   if v_cnt=0 then
      v_sql := 'ALTER TABLE public.' || p_bill || ' ADD COLUMN ' || v_workflow_state_field || ' integer default 0';
      execute  v_sql; 
   end if;
   v_sql := 'select COALESCE(' || v_workflow_state_field || ',0) From public.' || p_bill || ' where  id=' || p_billid;
   execute  v_sql into v_workflow_stateid; 
   if v_workflow_stateid=0 then---草稿状态，提交审核
      -------------------------------------检查权限
      if v_billuserid != p_userid then
         SELECT count(users.id) into v_cnt
         FROM users,groups 
         WHERE users.id=p_userid and groups.id = ANY (users.groups) and 'admin'=ANY(groups.permissions);
         if v_cnt=0 then
             select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'没有权限，只有制单人或系统管理员可以提交审批' as message ) t;
             return v_data::json; 
         end if;
      end if;
     ---------------------------------------------------- 
	select min(workflow_stateid) 
	into v_workflow_stateid
	from workflow_bill_state where id in
	(select min(id) from   workflow_bill_state where workflowid=v_workflowid);

        ---------------------
	   v_sql := 'select COALESCE(min(id),0) from  
	   (select public.workflowtabsel(''' || p_bill || ''',condition,''id'',''id'',''' || p_billid || ''') as billid , id 
	   from  public.workflow_transition where workflowid=' || v_workflowid || ' and workflow_stateid=' || v_workflow_stateid || ' and workflow_action_masterid= 1) aa
	   where aa.billid>0';
	   execute  v_sql into v_workflow_transitionid; 
        if public.alertsadd(p_bill,p_billid,1,v_workflow_transitionid,1,p_userid,p_altermsg,p_org_id) then ---添加提醒功能
        end if;
     ------------开始提交审核-----------
     if p_bill='contracts' then
	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_billid,'提交审核('|| code || ' ' || name ||')',now(),
	   now(),'contracts' ,id from public.contracts where id=p_billid;
     end if;
     ---------------------------------
        if public.workflowchange(p_bill,p_billid,1,p_workflow_action_masterid,p_userid) then
	      v_sql := 'update ' || p_bill || ' set status=1,' || v_workflow_state_field || '=' || v_workflow_stateid || ' where id=' || p_billid;
	      execute  v_sql; 
	      
	       insert into public.workflow_action
		(updated_at,created_at,userid,docstatus,
		  workflowid,billid,bill_enum,
		  workflow_stateid,completed_by,status,
		  user_tags,comments,assign,liked_by,org_id)
		select p_billdate,now(),p_userid,1,
		  v_workflowid,p_billid,p_bill,
		  v_workflow_stateid,0,0 as status,
		  '','','','',p_org_id;
	      select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,'审核成功' as message ) t;
	       return v_data::json; 

        else
	      select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'审核失败(workflowchange)' as message ) t;
	       return v_data::json; 
        end if;
   end if;
   v_sql := 'select COALESCE(min(id),0) from  
   (select public.workflowtabsel(''' || p_bill || ''',condition,''id'',''id'',''' || p_billid || ''') as billid , id 
   from  public.workflow_transition where workflowid=' || v_workflowid || ' and workflow_stateid=' || v_workflow_stateid || ' and workflow_action_masterid= ' || p_workflow_action_masterid || ') aa
   where aa.billid>0';
   execute  v_sql into v_workflow_transitionid; 
   if v_workflow_transitionid =0 then---找不到下一状态直接回到草稿状态或审批
      -------------------------------------检查权限
      if v_billuserid = p_userid and p_workflow_action_masterid=2 and v_status<>2 then
      else
           select count(workflow_stateid) into v_cnt
	   from  public.workflow_action where workflowid= v_workflowid and bill_enum=  p_bill  and billid = p_billid and workflow_stateid= v_workflow_stateid and
	   created_at<(select max(created_at) from  public.workflow_action where  workflowid= v_workflowid and bill_enum=  p_bill  and billid = p_billid and workflow_stateid= v_workflow_stateid) ;
	   if v_cnt =0 then
	         -------------------------------------检查权限
		    select count(users.id) into v_cnt from 
		    (select allow_user,allow_group from  public.workflow_transition where  workflowid=v_workflowid and workflow_stateid=v_workflow_stateid and workflow_action_masterid=1) obj,users
		    where ((users.id = any(obj.allow_user)) 
		    or (users.groups && obj.allow_group))
		    and users.id=p_userid ;
		    if v_cnt =0 then
			select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'没有权限' as message ) t;
			return v_data::json; 
		    end if;
	   else
	        select max(workflow_stateid) into v_next_workflow_stateid
		from  public.workflow_action where workflowid= v_workflowid and bill_enum=  p_bill  and billid = p_billid and workflow_stateid= v_workflow_stateid and
		created_at<(select max(created_at) from  public.workflow_action where  workflowid= v_workflowid and bill_enum=  p_bill  and billid = p_billid and workflow_stateid= v_workflow_stateid) 
		 order by updated_at desc limit 1 ;

		-------------------------------------检查权限
		select count(users.id) into v_cnt from 
		(select allow_user,allow_group from  public.workflow_transition where  workflowid=v_workflowid and workflow_stateid=v_next_workflow_stateid and next_workflow_stateid=v_workflow_stateid) obj,users
		where ((users.id = any(obj.allow_user)) 
		or (users.groups && obj.allow_group))
		and users.id=p_userid ;
		if v_cnt =0 then
		select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'没有权限' as message ) t;
		return v_data::json; 
		end if;
	   end if;
      end if;
     ---------------------------------------------------- 
        if p_workflow_action_masterid=1 then
            v_status :=2;
        else
            v_status :=0;
            v_workflow_stateid :=0;
        end if;
        if public.alertsadd(p_bill,p_billid,v_status,v_workflow_transitionid,0,p_userid,p_altermsg,p_org_id) then ---添加提醒功能
        end if;
        ------------------xiugaizhuangtai
	   update  public.workflow_action
	   set completed_by=p_userid,status=case when p_workflow_action_masterid=1 then 2 else 1 end,comments=p_message,updated_at=p_billdate
	   where workflowid=v_workflowid and bill_enum=p_bill and billid=p_billid and status=0;
   -------------------------------

     ------------开始提交审核-----------
     if p_bill='contracts' then
	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_billid,case when v_status=2 then '已审核('|| code || ' ' || name ||')' else '已驳回('|| code || ' ' || name ||')' end,now(),
	   now(),'contracts' ,id from public.contracts where id=p_billid;
     end if;
     ---------------------------------
        if public.workflowchange(p_bill,p_billid,v_status,p_workflow_action_masterid,p_userid) then
	      v_sql := 'update ' || p_bill || ' set status=' || v_status || ',' || v_workflow_state_field || '=' || v_workflow_stateid || ' where id=' || p_billid;
	      execute  v_sql; 
	      update  public.workflow_action
	      set completed_by=p_userid,status=2,comments=p_message,updated_at=p_billdate
	      where workflowid=v_workflowid and bill_enum=p_bill and billid=p_billid and workflow_stateid=v_workflow_stateid
	      and status=0;
	      
	      select array_to_json(array_agg(row_to_json(t))) into v_data from (
		select 0 as flag,v_workflow_action_mastername ||'完成' as message ) t;
	       return v_data::json; 
         else
	      select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'审核失败(workflowchange)' as message ) t;
	       return v_data::json; 
         end if;
   end if;
   -------------------------------------查找下一动作ID
   select next_workflow_stateid 
    into v_next_workflow_stateid
   from  public.workflow_transition where id=v_workflow_transitionid;
   
      -------------------------------------检查权限
      if v_billuserid = p_userid and p_workflow_action_masterid=2  and v_status<>2 then
      else
	    select count(users.id) into v_cnt from 
	    (select allow_user,allow_group from  public.workflow_transition where id=v_workflow_transitionid) obj,users
	    where ((users.id = any(obj.allow_user)) 
	    or (users.groups && obj.allow_group))
	    and users.id=p_userid ;
	    if v_cnt =0 then
		select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'没有权限' as message ) t;
		return v_data::json; 
	    end if;
      end if;
     ---------------------------------------------------- 
   select doc_status
    into v_status
   from   workflow_bill_state where workflowid=v_workflowid and workflow_stateid=v_next_workflow_stateid;

   v_sql := 'update ' || p_bill || ' set status=' || v_status || ',' || v_workflow_state_field || '=' || v_next_workflow_stateid || ' where id=' || p_billid;
   execute  v_sql; 
   update  public.workflow_action
   set completed_by=p_userid,status=2,comments=p_message,updated_at=p_billdate
   where workflowid=v_workflowid and bill_enum=p_bill and billid=p_billid and workflow_stateid=v_workflow_stateid
   and status=0;
   if v_status !=2 then----如果不是终审，添加下一状态
       insert into public.workflow_action
	(updated_at,created_at,userid,docstatus,
	  workflowid,billid,bill_enum,
	  workflow_stateid,completed_by,status,
	  user_tags,comments,assign,liked_by,org_id)
	select p_billdate,now(),p_userid,1,
	  v_workflowid,p_billid,p_bill,
	  v_next_workflow_stateid,0,0 as status,
	  '','','','',p_org_id;
   else
       insert into public.workflow_action
	(updated_at,created_at,userid,docstatus,
	  workflowid,billid,bill_enum,
	  workflow_stateid,completed_by,status,
	  user_tags,comments,assign,liked_by,org_id)
	select p_billdate,now(),p_userid,1,
	  v_workflowid,p_billid,p_bill,
	  v_next_workflow_stateid,p_userid,2 as status,
	  '','','','',p_org_id;
	 ------------开始提交审核-----------
	 if p_bill='contracts' then
		   insert into public.contractactivity(userid,contractsid,message,updated_at,
		   created_at,sourcetype ,sourceid)
		   select p_userid,p_billid,'已审核('|| code || ' ' || name ||')',now(),
		   now(),'contracts' ,id from public.contracts where id=p_billid;
	  end if;
	  ---------------------------------
	  
   end if;
        ---------------------
	   v_sql := 'select COALESCE(min(id),0) from  
	   (select public.workflowtabsel(''' || p_bill || ''',condition,''id'',''id'',''' || p_billid || ''') as billid , id 
	   from  public.workflow_transition where workflowid=' || v_workflowid || ' and workflow_stateid=' || v_next_workflow_stateid || ' and workflow_action_masterid= 1) aa
	   where aa.billid>0';
	   execute  v_sql into v_workflow_transitionid; 
        if public.alertsadd(p_bill,p_billid,v_status,v_workflow_transitionid,0,p_userid,p_altermsg,p_org_id) then ---添加提醒功能
       end if;
   select array_to_json(array_agg(row_to_json(t))) into v_data from (
    select 0 as flag,v_workflow_action_mastername ||'完成' as message ) t;

   return v_data::json; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
###############################################11workflowedit
    strsql="""CREATE OR REPLACE FUNCTION public.workflowedit(
    v_bill character varying,
    v_billid integer,
    v_userid integer,
    p_org_id integer)
  RETURNS json AS
$BODY$
declare  
    v_workflowid integer;  ----工作流程ID
    v_workflow_state_field   varchar(50);--流程状态字段
    v_workflow_stateid  integer;  ----工作流程状态ID
    v_status integer;---但据状态
    v_billuserid integer;---制单人
    v_cnt integer;
    v_sql text;
    v_data text;
    v_edit boolean;
    v_check boolean;
    v_del boolean;
begin
    v_edit := false;
    v_check := false;
    v_del := false;
--------------检查但据审核状态-------------
   v_sql := 'select COALESCE(min(status),0),COALESCE(min(userid),0) From public.' || v_bill || ' where  id=' || v_billid;
   execute  v_sql into v_status,v_billuserid; 
   if v_status=0 then---
	if v_billuserid=v_userid then---
	    v_sql :='select array_to_json(array_agg(row_to_json(t)))  from (select '|| v_edit ||' as edit, '|| v_del ||' as del, '|| v_check ||' as check,
	    false as colcontrol,''{}'' as allow_col ) t';
	    execute v_sql into v_data;
	    return v_data::json; 
	else
	    v_edit := true;
	    v_del :=  true;
	    v_sql :='select array_to_json(array_agg(row_to_json(t)))  from (select '|| v_edit ||' as edit, '|| v_del ||' as del, '|| v_check ||' as check,
	    false as colcontrol,''{}'' as allow_col ) t';
	    execute v_sql into v_data;
	    return v_data::json; 
	end if;
   end if;
   
   if v_status=2 then---
	    v_edit := true;
	    v_del :=  true;
	    v_check := true;
	    v_sql :='select array_to_json(array_agg(row_to_json(t)))  from (select '|| v_edit ||' as edit, '|| v_del ||' as del, '|| v_check ||' as check,
	    false as colcontrol,''{}'' as allow_col ) t';
	    execute v_sql into v_data;
	    return v_data::json; 
   end if;
   v_del :=  true;
-----------------------------------------------------------
   select workflow_state_field,id
   into v_workflow_state_field,v_workflowid
   From workflow where org_id=p_org_id and is_active=true and bill_enum=v_bill  and (orgunitid=0 or orgunitid in(public.workflowtabsel(v_bill,'','departmentid','id',v_billid)));
   
   select count(column_name) 
   into v_cnt
   from information_schema.columns 
   where table_schema='public' and table_name=v_bill and column_name=v_workflow_state_field;
   if v_cnt=0 then
        select array_to_json(array_agg(row_to_json(t))) into v_data from (select false as flag ) t;
        return v_data::json; 
   end if;
   v_sql := 'select COALESCE(' || v_workflow_state_field || ',0) From public.' || v_bill || ' where  id=' || v_billid;
   execute  v_sql into v_workflow_stateid; 
   if v_workflow_stateid=0 then---
        v_edit := false;
	v_sql :='select array_to_json(array_agg(row_to_json(t)))  from (select '|| v_edit ||' as edit, '|| v_del ||' as del, '|| v_check ||' as check,
	false as colcontrol,''{}'' as allow_col ) t';
	execute v_sql into v_data;
        return v_data::json; 
   end if;
      -------------------------------------检查权限
    select count(users.id)
     into v_cnt from 
    (select allow_user,allow_group from  public.workflow_bill_state where  workflowid=v_workflowid and workflow_stateid=v_workflow_stateid) obj,users
    where ((users.id = any(obj.allow_user))
    or (users.groups && obj.allow_group))
    and users.id=v_userid ;
    if v_cnt =0 then
        v_edit := true;
	v_sql :='select array_to_json(array_agg(row_to_json(t)))  from (select '|| v_edit ||' as edit, '|| v_del ||' as del, '|| v_check ||' as check,
	false as colcontrol,''{}'' as allow_col ) t';
	execute v_sql into v_data;
        return v_data::json; 
     --   select array_to_json(array_agg(row_to_json(t))) into v_data from (select true as flag ) t;
     --   return v_data::json; 
    end if;
    v_edit := false;
    select array_to_json(array_agg(row_to_json(t))) 
     into v_data from (
    select false as edit, false as del,false as check,obj.allow_col,obj.colcontrol
     from 
    (select allow_user,allow_group,colcontrol,allow_col from  public.workflow_bill_state where  workflowid=v_workflowid and workflow_stateid=v_workflow_stateid) obj,users
    where ((users.id = any(obj.allow_user))
    or (users.groups && obj.allow_group))
    and users.id=v_userid  limit 1 ) t;
    
   -- v_sql :='select array_to_json(array_agg(row_to_json(t)))  from (select '|| v_edit ||' as edit, '|| v_del ||' as del, '|| v_check ||' as check,
   -- '|| v_allow_col ||' as allow_col,'|| v_colcontrol ||' as colcontrol ) t';
   -- execute v_sql into v_data;
    return v_data::json; 
  -- select array_to_json(array_agg(row_to_json(t))) into v_data from (select false as flag ) t;
  --- return v_data::json; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
##########################################12workflowtabsel
    strsql="""CREATE OR REPLACE FUNCTION public.workflowtabsel(
    p_bill character varying,
    p_condition text,
    p_fieldsel character varying,
    p_field character varying,
    p_fieldid integer)
  RETURNS integer AS
$BODY$
declare  
    v_cnt integer;
    v_sql text;
begin  

   v_sql := 'select COALESCE(min(' || p_fieldsel || '),0) from public.' || p_bill || ' where  ' || p_field || ' = '|| p_fieldid;
   if trim(p_condition) !='' then
	v_sql := v_sql || ' and ' || p_condition;
   end if;
   execute  v_sql into v_cnt; 
   RETURN v_cnt::integer; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;"""
    db.session.execute("DO $do$ BEGIN execute :sqlfun ; END $do$;",{"sqlfun": strsql})
    db.session.commit()
################end######################
    return default_org, user


@routes.route("/setup", methods=["GET", "POST"])
def setup():
    if current_org != None  and not settings.MULTI_ORG:
        return redirect("/")

    form = SetupForm(request.form)
    form.newsletter.data = True
    form.security_notifications.data = True

    if request.method == "POST" and form.validate():
        try:
            default_org, user = create_org(
                form.org_name.data, form.name.data, form.code.data, form.email.data, form.mobile.data, form.password.data,
                form.org_slug.data if settings.MULTI_ORG else "default"
            )
        except:
            flash('单位代码已被占用，请重新输入！', category="error")
            return render_template("multi_org_setup.html" if settings.MULTI_ORG else "setup.html", form=form)

        g.org = default_org
        login_user(user)

        # signup to newsletter if needed
        if form.newsletter.data or form.security_notifications:
            subscribe.delay(form.data)

        return redirect(url_for("redash.index", org_slug = form.org_slug.data if settings.MULTI_ORG else None))

    return render_template("multi_org_setup.html" if settings.MULTI_ORG else "setup.html", form=form)